package com.ruoyi.multab.utils;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONObject;

public class MulExcelUtils {
	
    private static final Logger log = LoggerFactory.getLogger(MulExcelUtils.class);
	
	//excel2003扩展名
    public static final String EXCEL03_EXTENSION = ".xls";
    //excel2007扩展名
    public static final String EXCEL07_EXTENSION = ".xlsx";
    
    private static NumberFormat numberFormat = NumberFormat.getNumberInstance();
	
	public static MulExcelObj readExcel(String fileName) throws Exception {
		
		MulExcelObj mulExcelObj = new MulExcelObj();
		
		FileInputStream fileInputStream = new FileInputStream(fileName);
		Workbook wb = WorkbookFactory.create(fileInputStream);
		
		Sheet sheetAt = wb.getSheetAt(0);
		int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
		Row row = sheetAt.getRow(0);
		short lastCellNum = row.getLastCellNum();
		mulExcelObj.setTotalCellNum(lastCellNum);
		mulExcelObj.setTotalRowNum(physicalNumberOfRows);
		
		
		JSONObject titleObj = new JSONObject();
		for(int i = 0;i<lastCellNum;i++) {
			Cell cell = row.getCell(i);
			cell.setCellType(CellType.STRING);
			String titleName = cell.getStringCellValue();
			log.debug("第一行，第"+(i+1)+"列="+titleName);
			
			titleObj.put("dataValue"+i, titleName);
			
		}
		mulExcelObj.addTitle(titleObj);
		log.info("总行数="+physicalNumberOfRows);
		
		Row dataRow = null;
		Cell dataCell = null;
		for(int i = 1;i<physicalNumberOfRows;i++) {
			dataRow = sheetAt.getRow(i);
			
			JSONObject rowObj = new JSONObject();
			rowObj.put("dataRowIndex", i);
			
			for(int j = 0;j<lastCellNum;j++) {
				dataCell = dataRow.getCell(j);
				String dataValue = getCellValue(dataCell);
				log.debug("第{}行,第{}列={}",i,j+1,dataValue);
				rowObj.put("dataValue"+j, dataValue);
			}
			mulExcelObj.addData(rowObj);
			
		}
		
		wb.close();
		fileInputStream.close();
		
		return mulExcelObj;
		
    }
	public static MulExcelObj readExcel(InputStream inputstream) throws Exception {
		
		MulExcelObj mulExcelObj = new MulExcelObj();
		
		Workbook wb = WorkbookFactory.create(inputstream);
		
		Sheet sheetAt = wb.getSheetAt(0);
		int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
		Row row = sheetAt.getRow(0);
		short lastCellNum = row.getLastCellNum();
		mulExcelObj.setTotalCellNum(lastCellNum);
		mulExcelObj.setTotalRowNum(physicalNumberOfRows);
		
		
		JSONObject titleObj = new JSONObject();
		for(int i = 0;i<lastCellNum;i++) {
			Cell cell = row.getCell(i);
			cell.setCellType(CellType.STRING);
			String titleName = cell.getStringCellValue();
			log.debug("第一行，第"+(i+1)+"列="+titleName);
			
			titleObj.put("dataValue"+i, titleName);
			
		}
		mulExcelObj.addTitle(titleObj);
		log.info("总行数="+physicalNumberOfRows);
		
		Row dataRow = null;
		Cell dataCell = null;
		for(int i = 1;i<physicalNumberOfRows;i++) {
			dataRow = sheetAt.getRow(i);
			
			JSONObject rowObj = new JSONObject();
			rowObj.put("dataRowIndex", i);
			
			for(int j = 0;j<lastCellNum;j++) {
				dataCell = dataRow.getCell(j);
				String dataValue = getCellValue(dataCell);
				log.debug("第{}行,第{}列={}",i,j+1,dataValue);
				rowObj.put("dataValue"+j, dataValue);
			}
			mulExcelObj.addData(rowObj);
			
		}
		
		wb.close();
		inputstream.close();
		
		return mulExcelObj;
		
    }
	public static String getCellValue(Cell cell) {
        if (null == cell) {
            return "";
        }
        Object value = null;
        CellType cellTypeEnum = cell.getCellTypeEnum();
        
        if(CellType.NUMERIC == cellTypeEnum) {
        	
        	double d = cell.getNumericCellValue();   
            value = numberFormat.format(d);
        	
        }else {
        	cell.setCellType(CellType.STRING);
        	value = cell.getStringCellValue();
        }
        return value == null ? "" : value.toString();
    }
	public static void main(String[] args) throws Exception {
//        String path="C:\\Users\\zhouwc\\Desktop\\multab\\模型1数据18列.xlsx";
        String path="C:\\Users\\zhouwc\\Desktop\\multab\\模型1数据18列.xls";
        MulExcelObj readExcel = MulExcelUtils.readExcel(path);
        
        List<JSONObject> titleArray = readExcel.getTitleArray();
        List<JSONObject> dataArray = readExcel.getDataArray();
        log.info(titleArray.toString());
        log.info(dataArray.toString());
        log.info("总列="+readExcel.getTotalCellNum());
        log.info("总行="+readExcel.getTotalRowNum());
    }

}
